package com.erclab.internal.xpresso.security;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.erclab.internal.xpresso.conf.Settings;
import com.erclab.internal.xpresso.dataAccess.ApplicationDataSource;
import com.erclab.internal.xpresso.dataAccess.Query;
import com.erclab.internal.xpresso.exceptions.DataSourceException;
import com.erclab.internal.xpresso.exceptions.DirectoryException;
import com.erclab.internal.xpresso.handlers.UserRolesHandler;
import com.erclab.internal.xpresso.security.User;

public class InternetMysqlDirectory implements UserDirectory {

	protected final String USER_QUERY = "SELECT * FROM usr_users WHERE userName=?";
	protected final String ROLES_QUERY = "SELECT RoleName FROM usp_user_permissions usp, " +
                " ser_security_roles ser WHERE ser.IdRole=usp.IdRole AND IdUser=?";
	protected final String INSERT_VISIT_LOG = "INSERT INTO vl_visit_log (VisitDate, UserName, IdUser, IpAddress, Application) " +
			"values(NOW(),?,?,?,?)";
	protected final String NEW_ROLE_QUERY = "INSERT INTO usp_user_permissions (IdUser, IdRole) values(?, " +
			"(SELECT IdRole from ser_security_roles where RoleName=?))";
	
	
	public void loadUserBean(User user) throws DirectoryException {
		if (user.getUserName() == null){
			user.setNetId("Guest");
			user.setUserName("Guest");
		} else {
			loadUser(user);
		}	
	}
	
	protected void loadUser(User user) throws DirectoryException {
		ApplicationDataSource ads = Settings.getRepository().getApplicationDataSource(Settings.getDirectoryIdDataSource());
		QueryRunner run = new QueryRunner((DataSource)ads.getTheDataSource());
		
		ResultSetHandler rsHandler = new BeanHandler(User.class);
		try {
			User userdb = (User)run.query(USER_QUERY, user.getUserName(), rsHandler);
			if (userdb == null){
				return;
			}
			user.setIdUser(userdb.getIdUser());
			user.setNetId("" + userdb.getUserName());
			user.setEmail(userdb.getEmail());
			user.setFirstName(userdb.getFirstName());
			user.setLastName(userdb.getLastName());
		}catch(SQLException ex){
			throw new DirectoryException("A problem ocurred loading user information: " + ex);			
		}
		rsHandler = new UserRolesHandler(user);
		try {
			run.query(ROLES_QUERY, "" + user.getIdUser(), rsHandler);
		}catch(SQLException ex){
			throw new DirectoryException("A problem ocurred loading the user roles: " + ex);			
		}
	}

	
	public void addVisitEntry(User user) throws DirectoryException {
		ApplicationDataSource ads = Settings.getRepository().getApplicationDataSource(Settings.getDirectoryIdDataSource());
		Query q = new Query();
		q.setQuery(INSERT_VISIT_LOG);
		q.setValues(new String[]{user.getUserName(), "" + user.getIdUser(), user.getIpAddress(), Settings.getProperty("application.name")});
		try {
			ads.executeQuery(q);
		} catch (DataSourceException e) {
			throw new DirectoryException("A problem ocurred: " + e);
		}
	}

	public void addRoleToUser(User user, String roleName) throws DirectoryException {
		ApplicationDataSource ads = Settings.getRepository().getApplicationDataSource(Settings.getDirectoryIdDataSource());
		Query q = new Query();
		q.setQuery(NEW_ROLE_QUERY);
		q.setValues(new String[]{"" + user.getIdUser(), roleName});
		try {
			ads.executeQuery(q);
		} catch (DataSourceException e) {
			throw new DirectoryException("A problem ocurred: " + e);
		}
		this.loadUser(user);
	}
	
}
